{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 第四阶段 - 第3讲：分组与聚合分析\n",
    "\n",
    "## 学习目标\n",
    "- 深入理解groupby的Split-Apply-Combine机制\n",
    "- 掌握单列和多列分组\n",
    "- 熟练使用agg()进行多函数聚合\n",
    "- 掌握pivot_table()数据透视表\n",
    "- 计算派生指标(同比、环比、占比、累计)\n",
    "- 对标Excel数据透视表功能\n",
    "\n",
    "**重要性**: ⭐⭐⭐⭐⭐ 这是数据分析最核心的技能！\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导入库\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from datetime import datetime, timedelta\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "# 设置\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.float_format', '{:.2f}'.format)\n",
    "plt.rcParams['font.sans-serif'] = ['Arial Unicode MS', 'SimHei']\n",
    "plt.rcParams['axes.unicode_minus'] = False\n",
    "\n",
    "print(\"✅ 环境配置完成\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 一、groupby()机制详解\n",
    "\n",
    "### 1.1 Split-Apply-Combine原理\n",
    "\n",
    "**核心思想**: 将数据分组处理，然后合并结果\n",
    "\n",
    "```\n",
    "原始数据\n",
    "    ↓\n",
    "Split (分组)\n",
    "    ↓\n",
    "Apply (应用函数)\n",
    "    ↓\n",
    "Combine (合并结果)\n",
    "```\n",
    "\n",
    "**举例说明**:\n",
    "```\n",
    "原始数据:\n",
    "产品    销售额\n",
    "手机    1000\n",
    "电脑    2000\n",
    "手机    1500\n",
    "电脑    2500\n",
    "\n",
    "Split (按产品分组):\n",
    "手机组: [1000, 1500]\n",
    "电脑组: [2000, 2500]\n",
    "\n",
    "Apply (求和):\n",
    "手机: 2500\n",
    "电脑: 4500\n",
    "\n",
    "Combine (合并):\n",
    "产品    总销售额\n",
    "手机    2500\n",
    "电脑    4500\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建完整的示例数据集\n",
    "np.random.seed(42)\n",
    "n = 200\n",
    "\n",
    "# 生成日期\n",
    "dates = pd.date_range('2024-01-01', periods=n, freq='D')\n",
    "\n",
    "# 生成数据\n",
    "sales_data = {\n",
    "    'date': dates,\n",
    "    'year': dates.year,\n",
    "    'month': dates.month,\n",
    "    'quarter': dates.quarter,\n",
    "    'order_id': [f'ORD{str(i).zfill(5)}' for i in range(1, n+1)],\n",
    "    'customer': np.random.choice(['张三', '李四', '王五', '赵六', '钱七', '孙八'], n),\n",
    "    'product': np.random.choice(['iPhone 15', 'MacBook Pro', 'iPad Air', 'AirPods Pro', 'Apple Watch'], n),\n",
    "    'category': np.random.choice(['手机', '电脑', '平板', '耳机', '智能手表'], n),\n",
    "    'region': np.random.choice(['华东', '华北', '华南', '华中', '西南', '西北', '东北'], n),\n",
    "    'sales_person': np.random.choice(['员工A', '员工B', '员工C', '员工D', '员工E'], n),\n",
    "    'quantity': np.random.randint(1, 10, n),\n",
    "    'unit_price': np.random.choice([999, 1999, 2999, 4999, 6999, 12999], n),\n",
    "    'discount': np.random.choice([0, 0.05, 0.1, 0.15, 0.2], n)\n",
    "}\n",
    "\n",
    "df = pd.DataFrame(sales_data)\n",
    "\n",
    "# 计算总金额\n",
    "df['amount'] = df['quantity'] * df['unit_price'] * (1 - df['discount'])\n",
    "\n",
    "# 添加成本和利润\n",
    "df['cost'] = df['quantity'] * df['unit_price'] * 0.6  # 假设成本率60%\n",
    "df['profit'] = df['amount'] - df['cost']\n",
    "\n",
    "print(f\"✅ 创建了{len(df)}条销售记录\")\n",
    "print(\"\\n数据预览:\")\n",
    "print(df.head(10))\n",
    "print(f\"\\n数据规模: {df.shape}\")\n",
    "print(f\"\\n列名: {df.columns.tolist()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2 单列分组\n",
    "\n",
    "**语法**: `df.groupby('列名')`\n",
    "\n",
    "**Excel对比**: 类似在数据透视表中将字段拖到\"行\"区域"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 基础分组 - 按产品分组\n",
    "print(\"=== 按产品分组 ===\")\n",
    "grouped = df.groupby('product')\n",
    "\n",
    "print(f\"分组对象类型: {type(grouped)}\")\n",
    "print(f\"分组数量: {grouped.ngroups}\")\n",
    "print(f\"分组key: {list(grouped.groups.keys())}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 单一聚合函数\n",
    "print(\"\\n各产品总销售额:\")\n",
    "product_sales = df.groupby('product')['amount'].sum()\n",
    "print(product_sales.sort_values(ascending=False))\n",
    "\n",
    "print(\"\\n各产品平均单价:\")\n",
    "product_avg = df.groupby('product')['unit_price'].mean()\n",
    "print(product_avg.sort_values(ascending=False))\n",
    "\n",
    "print(\"\\n各产品订单数:\")\n",
    "product_count = df.groupby('product').size()\n",
    "print(product_count.sort_values(ascending=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 查看每组的详细数据\n",
    "print(\"\\n查看iPhone组的前3条记录:\")\n",
    "for name, group in grouped:\n",
    "    if name == 'iPhone 15':\n",
    "        print(group[['product', 'quantity', 'unit_price', 'amount']].head(3))\n",
    "        break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.3 多列分组\n",
    "\n",
    "**语法**: `df.groupby(['列1', '列2'])`\n",
    "\n",
    "**Excel对比**: 在数据透视表中同时拖动多个字段到\"行\"区域"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 多列分组 - 按地区和产品分组\n",
    "print(\"=== 按地区和产品分组 ===\")\n",
    "multi_grouped = df.groupby(['region', 'product'])['amount'].sum()\n",
    "print(multi_grouped.head(15))\n",
    "print(f\"\\n多列分组后的索引类型: {type(multi_grouped.index)}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 重置索引使结果更易读\n",
    "print(\"\\n重置索引后:\")\n",
    "result = df.groupby(['region', 'product'])['amount'].sum().reset_index()\n",
    "result.columns = ['地区', '产品', '总销售额']\n",
    "result = result.sort_values('总销售额', ascending=False)\n",
    "print(result.head(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 二、聚合函数详解\n",
    "\n",
    "### 2.1 agg()多函数聚合\n",
    "\n",
    "**优势**: 一次性对多列应用多个函数\n",
    "\n",
    "**Excel对比**: 需要分别拖动多次,Pandas一次搞定"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 对单列应用多个函数\n",
    "print(\"=== 对amount列应用多个统计函数 ===\")\n",
    "product_stats = df.groupby('product')['amount'].agg(['sum', 'mean', 'median', 'std', 'min', 'max', 'count'])\n",
    "product_stats.columns = ['总销售额', '平均值', '中位数', '标准差', '最小值', '最大值', '订单数']\n",
    "print(product_stats.round(2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 对多列应用多个函数\n",
    "print(\"\\n=== 对多列分别聚合 ===\")\n",
    "multi_col_agg = df.groupby('product').agg({\n",
    "    'amount': ['sum', 'mean', 'count'],\n",
    "    'quantity': ['sum', 'mean'],\n",
    "    'profit': ['sum', 'mean']\n",
    "})\n",
    "print(multi_col_agg.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 命名聚合 (更清晰的列名)\n",
    "print(\"\\n=== 命名聚合 ===\")\n",
    "product_summary = df.groupby('product').agg(\n",
    "    总销售额=('amount', 'sum'),\n",
    "    平均销售额=('amount', 'mean'),\n",
    "    订单数=('order_id', 'count'),\n",
    "    总销量=('quantity', 'sum'),\n",
    "    总利润=('profit', 'sum'),\n",
    "    利润率=('profit', lambda x: (x.sum() / df.loc[x.index, 'amount'].sum() * 100))\n",
    ").round(2)\n",
    "\n",
    "product_summary = product_summary.sort_values('总销售额', ascending=False)\n",
    "print(product_summary)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2 自定义聚合函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 自定义函数 - 计算变异系数(CV)\n",
    "def coefficient_of_variation(x):\n",
    "    \"\"\"变异系数 = 标准差/均值\"\"\"\n",
    "    return x.std() / x.mean() if x.mean() != 0 else 0\n",
    "\n",
    "# 自定义函数 - 计算极差\n",
    "def range_func(x):\n",
    "    \"\"\"极差 = 最大值 - 最小值\"\"\"\n",
    "    return x.max() - x.min()\n",
    "\n",
    "print(\"=== 使用自定义函数 ===\")\n",
    "custom_agg = df.groupby('product')['amount'].agg([\n",
    "    ('均值', 'mean'),\n",
    "    ('标准差', 'std'),\n",
    "    ('变异系数', coefficient_of_variation),\n",
    "    ('极差', range_func)\n",
    "]).round(2)\n",
    "\n",
    "print(custom_agg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3 常用聚合函数汇总\n",
    "\n",
    "| 函数 | 说明 | Excel等价 |\n",
    "|------|------|----------|\n",
    "| `sum()` | 求和 | `SUM()` |\n",
    "| `mean()` | 均值 | `AVERAGE()` |\n",
    "| `median()` | 中位数 | `MEDIAN()` |\n",
    "| `std()` | 标准差 | `STDEV()` |\n",
    "| `var()` | 方差 | `VAR()` |\n",
    "| `min()` | 最小值 | `MIN()` |\n",
    "| `max()` | 最大值 | `MAX()` |\n",
    "| `count()` | 计数 | `COUNT()` |\n",
    "| `size()` | 行数(包括NaN) | `COUNTA()` |\n",
    "| `nunique()` | 唯一值个数 | 需要组合函数 |\n",
    "| `first()` | 第一个值 | `INDEX()` |\n",
    "| `last()` | 最后一个值 | `INDEX()` |\n",
    "| `quantile()` | 分位数 | `PERCENTILE()` |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 三、数据透视表 pivot_table()\n",
    "\n",
    "### 3.1 pivot_table()基础\n",
    "\n",
    "**核心**: 最接近Excel数据透视表的功能！\n",
    "\n",
    "**语法**:\n",
    "```python\n",
    "pd.pivot_table(\n",
    "    data,              # 数据源\n",
    "    values,            # 值字段\n",
    "    index,             # 行字段\n",
    "    columns,           # 列字段\n",
    "    aggfunc,           # 聚合函数\n",
    "    fill_value,        # 填充空值\n",
    "    margins            # 是否显示总计\n",
    ")\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 基础透视表 - 地区×产品 的销售额\n",
    "print(\"=== 基础透视表: 地区×产品 ===\")\n",
    "pivot1 = pd.pivot_table(\n",
    "    df,\n",
    "    values='amount',\n",
    "    index='region',\n",
    "    columns='product',\n",
    "    aggfunc='sum',\n",
    "    fill_value=0\n",
    ")\n",
    "print(pivot1.round(0))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 添加总计行和总计列\n",
    "print(\"\\n=== 带总计的透视表 ===\")\n",
    "pivot2 = pd.pivot_table(\n",
    "    df,\n",
    "    values='amount',\n",
    "    index='region',\n",
    "    columns='product',\n",
    "    aggfunc='sum',\n",
    "    fill_value=0,\n",
    "    margins=True,        # 添加总计\n",
    "    margins_name='总计'\n",
    ")\n",
    "print(pivot2.round(0))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 多个聚合函数\n",
    "print(\"\\n=== 多函数透视表 ===\")\n",
    "pivot3 = pd.pivot_table(\n",
    "    df,\n",
    "    values='amount',\n",
    "    index='region',\n",
    "    columns='product',\n",
    "    aggfunc=['sum', 'mean', 'count'],\n",
    "    fill_value=0\n",
    ")\n",
    "print(pivot3.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 多个值字段\n",
    "print(\"\\n=== 多值字段透视表 ===\")\n",
    "pivot4 = pd.pivot_table(\n",
    "    df,\n",
    "    values=['amount', 'quantity', 'profit'],\n",
    "    index='region',\n",
    "    columns='product',\n",
    "    aggfunc='sum',\n",
    "    fill_value=0\n",
    ")\n",
    "print(pivot4['amount'].round(0))  # 只显示销售额部分"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2 多级索引透视表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 行和列都使用多个字段\n",
    "print(\"=== 多级索引透视表 ===\")\n",
    "pivot5 = pd.pivot_table(\n",
    "    df,\n",
    "    values='amount',\n",
    "    index=['region', 'customer'],  # 多级行索引\n",
    "    columns='product',\n",
    "    aggfunc='sum',\n",
    "    fill_value=0\n",
    ")\n",
    "print(pivot5.head(20))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 月度×产品 销售趋势\n",
    "print(\"\\n=== 月度销售趋势透视表 ===\")\n",
    "pivot_monthly = pd.pivot_table(\n",
    "    df,\n",
    "    values='amount',\n",
    "    index='month',\n",
    "    columns='product',\n",
    "    aggfunc='sum',\n",
    "    fill_value=0\n",
    ")\n",
    "print(pivot_monthly.round(0))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3 透视表可视化"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 热力图展示透视表\n",
    "fig, axes = plt.subplots(1, 2, figsize=(16, 6))\n",
    "\n",
    "# 地区×产品 热力图\n",
    "pivot_heatmap = pd.pivot_table(\n",
    "    df, values='amount', index='region', columns='product',\n",
    "    aggfunc='sum', fill_value=0\n",
    ")\n",
    "sns.heatmap(pivot_heatmap, annot=True, fmt='.0f', cmap='YlOrRd', ax=axes[0])\n",
    "axes[0].set_title('Regional Sales by Product (Heatmap)', fontsize=14, fontweight='bold')\n",
    "\n",
    "# 月度销售趋势图\n",
    "pivot_monthly.plot(kind='line', ax=axes[1], marker='o', linewidth=2)\n",
    "axes[1].set_title('Monthly Sales Trend by Product', fontsize=14, fontweight='bold')\n",
    "axes[1].set_xlabel('Month')\n",
    "axes[1].set_ylabel('Sales Amount')\n",
    "axes[1].legend(title='Product', bbox_to_anchor=(1.05, 1))\n",
    "axes[1].grid(alpha=0.3)\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 四、派生指标计算\n",
    "\n",
    "### 4.1 同比增长率\n",
    "\n",
    "**定义**: 与去年同期相比的增长率\n",
    "\n",
    "**公式**: `(今年 - 去年) / 去年 × 100%`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建两年数据用于同比分析\n",
    "# 2023年数据\n",
    "df_2023 = df.copy()\n",
    "df_2023['year'] = 2023\n",
    "df_2023['date'] = df_2023['date'] - pd.DateOffset(years=1)\n",
    "\n",
    "# 2024年数据 (稍微增加20%)\n",
    "df_2024 = df.copy()\n",
    "df_2024['amount'] = df_2024['amount'] * 1.2\n",
    "\n",
    "# 合并\n",
    "df_yoy = pd.concat([df_2023, df_2024], ignore_index=True)\n",
    "\n",
    "print(f\"✅ 创建了{len(df_yoy)}条两年数据\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 计算同比增长率\n",
    "print(\"=== 产品同比增长率分析 ===\")\n",
    "\n",
    "# 按年份和产品分组\n",
    "yoy_sales = df_yoy.groupby(['year', 'product'])['amount'].sum().unstack(level=0)\n",
    "yoy_sales.columns = ['2023年', '2024年']\n",
    "\n",
    "# 计算同比增长率\n",
    "yoy_sales['同比增长率(%)'] = ((yoy_sales['2024年'] - yoy_sales['2023年']) / yoy_sales['2023年'] * 100).round(2)\n",
    "yoy_sales['增长金额'] = (yoy_sales['2024年'] - yoy_sales['2023年']).round(0)\n",
    "\n",
    "print(yoy_sales.sort_values('同比增长率(%)', ascending=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.2 环比增长率\n",
    "\n",
    "**定义**: 与上一期(通常是上个月)相比的增长率\n",
    "\n",
    "**公式**: `(本期 - 上期) / 上期 × 100%`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 计算月度环比\n",
    "print(\"=== 月度环比增长率 ===\")\n",
    "\n",
    "# 按月份汇总\n",
    "monthly_sales = df.groupby('month')['amount'].sum().reset_index()\n",
    "monthly_sales.columns = ['月份', '销售额']\n",
    "\n",
    "# 计算环比\n",
    "monthly_sales['上月销售额'] = monthly_sales['销售额'].shift(1)\n",
    "monthly_sales['环比增长率(%)'] = ((monthly_sales['销售额'] - monthly_sales['上月销售额']) / monthly_sales['上月销售额'] * 100).round(2)\n",
    "monthly_sales['环比增长额'] = (monthly_sales['销售额'] - monthly_sales['上月销售额']).round(0)\n",
    "\n",
    "print(monthly_sales)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 使用pct_change()快捷计算环比\n",
    "print(\"\\n=== 使用pct_change()计算环比 ===\")\n",
    "monthly_sales['环比增长率_v2(%)'] = (monthly_sales['销售额'].pct_change() * 100).round(2)\n",
    "print(monthly_sales[['月份', '销售额', '环比增长率(%)', '环比增长率_v2(%)']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.3 占比分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 各产品销售额占比\n",
    "print(\"=== 产品销售额占比 ===\")\n",
    "\n",
    "product_sales = df.groupby('product')['amount'].sum().reset_index()\n",
    "product_sales.columns = ['产品', '销售额']\n",
    "\n",
    "# 计算占比\n",
    "total_sales = product_sales['销售额'].sum()\n",
    "product_sales['占比(%)'] = (product_sales['销售额'] / total_sales * 100).round(2)\n",
    "\n",
    "# 计算累计占比 (帕累托分析)\n",
    "product_sales = product_sales.sort_values('销售额', ascending=False)\n",
    "product_sales['累计占比(%)'] = product_sales['占比(%)'].cumsum().round(2)\n",
    "\n",
    "print(product_sales)\n",
    "print(f\"\\n前2个产品占比: {product_sales.iloc[:2]['占比(%)'].sum():.2f}%\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化帕累托图\n",
    "fig, ax1 = plt.subplots(figsize=(12, 6))\n",
    "\n",
    "# 柱状图 - 销售额\n",
    "ax1.bar(range(len(product_sales)), product_sales['销售额'], color='steelblue', alpha=0.7)\n",
    "ax1.set_xlabel('Product', fontsize=12)\n",
    "ax1.set_ylabel('Sales Amount', fontsize=12, color='steelblue')\n",
    "ax1.tick_params(axis='y', labelcolor='steelblue')\n",
    "ax1.set_xticks(range(len(product_sales)))\n",
    "ax1.set_xticklabels(product_sales['产品'], rotation=45, ha='right')\n",
    "\n",
    "# 折线图 - 累计占比\n",
    "ax2 = ax1.twinx()\n",
    "ax2.plot(range(len(product_sales)), product_sales['累计占比(%)'], \n",
    "         color='red', marker='o', linewidth=2, markersize=8)\n",
    "ax2.axhline(y=80, color='red', linestyle='--', alpha=0.5, label='80%线')\n",
    "ax2.set_ylabel('Cumulative Percentage (%)', fontsize=12, color='red')\n",
    "ax2.tick_params(axis='y', labelcolor='red')\n",
    "ax2.set_ylim([0, 105])\n",
    "ax2.legend(loc='lower right')\n",
    "\n",
    "plt.title('Pareto Chart: Product Sales Analysis', fontsize=14, fontweight='bold')\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.4 累计值计算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 月度累计销售额\n",
    "print(\"=== 月度累计销售额 ===\")\n",
    "\n",
    "monthly = df.groupby('month')['amount'].sum().reset_index()\n",
    "monthly.columns = ['月份', '当月销售额']\n",
    "\n",
    "# 累计销售额\n",
    "monthly['累计销售额'] = monthly['当月销售额'].cumsum()\n",
    "\n",
    "# 累计占全年比例\n",
    "year_total = monthly['当月销售额'].sum()\n",
    "monthly['累计占比(%)'] = (monthly['累计销售额'] / year_total * 100).round(2)\n",
    "\n",
    "print(monthly)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.5 移动平均(Rolling Average)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 7日移动平均\n",
    "print(\"=== 7日移动平均 ===\")\n",
    "\n",
    "daily_sales = df.groupby('date')['amount'].sum().reset_index()\n",
    "daily_sales.columns = ['日期', '销售额']\n",
    "\n",
    "# 计算7日移动平均\n",
    "daily_sales['7日均值'] = daily_sales['销售额'].rolling(window=7).mean().round(2)\n",
    "daily_sales['30日均值'] = daily_sales['销售额'].rolling(window=30).mean().round(2)\n",
    "\n",
    "print(daily_sales.tail(15))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化移动平均\n",
    "plt.figure(figsize=(14, 6))\n",
    "plt.plot(daily_sales['日期'], daily_sales['销售额'], alpha=0.3, label='Daily Sales', linewidth=1)\n",
    "plt.plot(daily_sales['日期'], daily_sales['7日均值'], label='7-day MA', linewidth=2)\n",
    "plt.plot(daily_sales['日期'], daily_sales['30日均值'], label='30-day MA', linewidth=2)\n",
    "plt.title('Sales Trend with Moving Averages', fontsize=14, fontweight='bold')\n",
    "plt.xlabel('Date')\n",
    "plt.ylabel('Sales Amount')\n",
    "plt.legend()\n",
    "plt.grid(alpha=0.3)\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 五、实战案例\n",
    "\n",
    "### 案例1: RFM客户分层分析\n",
    "\n",
    "**RFM模型**:\n",
    "- **R (Recency)**: 最近一次购买时间\n",
    "- **F (Frequency)**: 购买频次\n",
    "- **M (Monetary)**: 购买金额"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# RFM分析\n",
    "print(\"=== RFM客户分层分析 ===\")\n",
    "\n",
    "# 设定分析日期\n",
    "analysis_date = df['date'].max() + pd.Timedelta(days=1)\n",
    "\n",
    "# 计算RFM指标\n",
    "rfm = df.groupby('customer').agg({\n",
    "    'date': lambda x: (analysis_date - x.max()).days,  # R: 距离最后购买天数\n",
    "    'order_id': 'count',                                # F: 购买次数\n",
    "    'amount': 'sum'                                     # M: 总金额\n",
    "})\n",
    "\n",
    "rfm.columns = ['最近购买(天)', '购买次数', '总消费']\n",
    "\n",
    "# RFM评分 (1-5分,5分最好)\n",
    "rfm['R_score'] = pd.qcut(rfm['最近购买(天)'], 5, labels=[5,4,3,2,1])  # 越小越好\n",
    "rfm['F_score'] = pd.qcut(rfm['购买次数'].rank(method='first'), 5, labels=[1,2,3,4,5])  # 越大越好\n",
    "rfm['M_score'] = pd.qcut(rfm['总消费'], 5, labels=[1,2,3,4,5])  # 越大越好\n",
    "\n",
    "# 综合得分\n",
    "rfm['RFM_score'] = rfm['R_score'].astype(int) + rfm['F_score'].astype(int) + rfm['M_score'].astype(int)\n",
    "\n",
    "# 客户分层\n",
    "def rfm_segment(row):\n",
    "    if row['RFM_score'] >= 12:\n",
    "        return '重要价值客户'\n",
    "    elif row['RFM_score'] >= 9:\n",
    "        return '重要发展客户'\n",
    "    elif row['RFM_score'] >= 6:\n",
    "        return '重要保持客户'\n",
    "    else:\n",
    "        return '一般客户'\n",
    "\n",
    "rfm['客户分层'] = rfm.apply(rfm_segment, axis=1)\n",
    "\n",
    "print(\"\\nRFM分析结果:\")\n",
    "print(rfm.sort_values('RFM_score', ascending=False))\n",
    "\n",
    "print(\"\\n客户分层统计:\")\n",
    "segment_summary = rfm.groupby('客户分层').agg({\n",
    "    '客户分层': 'count',\n",
    "    '总消费': 'sum'\n",
    "}).rename(columns={'客户分层': '客户数'})\n",
    "segment_summary['占比(%)'] = (segment_summary['客户数'] / segment_summary['客户数'].sum() * 100).round(2)\n",
    "print(segment_summary)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 案例2: 销售漏斗分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 模拟销售漏斗数据\n",
    "funnel_data = {\n",
    "    '阶段': ['访客', '浏览商品', '加入购物车', '下单', '支付成功'],\n",
    "    '人数': [10000, 6000, 2000, 800, 600]\n",
    "}\n",
    "funnel = pd.DataFrame(funnel_data)\n",
    "\n",
    "# 计算转化率\n",
    "funnel['转化率(%)'] = (funnel['人数'] / funnel['人数'].iloc[0] * 100).round(2)\n",
    "funnel['流失率(%)'] = (100 - funnel['转化率(%)']).round(2)\n",
    "\n",
    "# 计算环节转化率\n",
    "funnel['环节转化率(%)'] = (funnel['人数'] / funnel['人数'].shift(1) * 100).round(2)\n",
    "\n",
    "print(\"=== 销售漏斗分析 ===\")\n",
    "print(funnel)\n",
    "\n",
    "print(\"\\n关键发现:\")\n",
    "print(f\"总体转化率: {funnel['转化率(%)'].iloc[-1]}%\")\n",
    "print(f\"最大流失环节: {funnel.loc[funnel['环节转化率(%)'].idxmin(), '阶段']} ({100-funnel['环节转化率(%)'].min():.2f}%流失)\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 六、Pandas vs Excel 数据透视表对比\n",
    "\n",
    "### 功能对照表\n",
    "\n",
    "| 功能 | Excel | Pandas |\n",
    "|------|-------|--------|\n",
    "| 创建透视表 | 插入→数据透视表 | `pivot_table()` 或 `groupby()` |\n",
    "| 拖动字段 | 鼠标拖动 | 指定 index/columns 参数 |\n",
    "| 选择聚合函数 | 下拉选择 | aggfunc 参数 |\n",
    "| 多个聚合函数 | 重复拖动字段 | aggfunc=[func1, func2] |\n",
    "| 筛选 | 筛选器 | 提前过滤DataFrame |\n",
    "| 排序 | 点击排序 | `sort_values()` |\n",
    "| 显示总计 | 勾选设计选项 | margins=True |\n",
    "| 计算字段 | 插入计算字段 | 直接计算新列 |\n",
    "| 条件格式 | 手动设置 | 可视化库(seaborn) |\n",
    "| 刷新数据 | 手动刷新 | 重新运行代码 |\n",
    "| 导出结果 | 另存为 | `to_excel()`, `to_csv()` |\n",
    "\n",
    "### Pandas优势\n",
    "\n",
    "1. **自动化**: 一次编写,重复使用\n",
    "2. **灵活性**: 复杂计算、自定义函数\n",
    "3. **处理大数据**: 百万行轻松处理\n",
    "4. **可重现**: 代码记录所有步骤\n",
    "5. **与其他分析结合**: 统计、建模、可视化\n",
    "\n",
    "### Excel优势\n",
    "\n",
    "1. **可视化操作**: 拖拽即可\n",
    "2. **即时反馈**: 实时看到结果\n",
    "3. **易上手**: 无需编程\n",
    "4. **商务沟通**: 格式丰富\n",
    "\n",
    "### 最佳实践\n",
    "\n",
    "```\n",
    "数据处理和分析 → Pandas\n",
    "        ↓\n",
    "导出结果到Excel\n",
    "        ↓\n",
    "美化格式和展示 → Excel\n",
    "        ↓\n",
    "商务汇报\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 七、综合练习\n",
    "\n",
    "### 练习1: 完整的销售分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 任务: 生成一份完整的销售分析报告\n",
    "print(\"=\"*70)\n",
    "print(\"销售数据分析报告\")\n",
    "print(\"=\"*70)\n",
    "\n",
    "# 1. 总体指标\n",
    "print(\"\\n【一、总体业绩】\")\n",
    "print(f\"  报告期间: {df['date'].min().date()} 至 {df['date'].max().date()}\")\n",
    "print(f\"  总销售额: ¥{df['amount'].sum():,.0f}\")\n",
    "print(f\"  总订单数: {len(df):,}\")\n",
    "print(f\"  平均客单价: ¥{df['amount'].mean():,.2f}\")\n",
    "print(f\"  总利润: ¥{df['profit'].sum():,.0f}\")\n",
    "print(f\"  平均利润率: {(df['profit'].sum()/df['amount'].sum()*100):.2f}%\")\n",
    "\n",
    "# 2. 产品分析\n",
    "print(\"\\n【二、产品分析】\")\n",
    "product_perf = df.groupby('product').agg({\n",
    "    'amount': 'sum',\n",
    "    'profit': 'sum',\n",
    "    'order_id': 'count'\n",
    "}).sort_values('amount', ascending=False)\n",
    "product_perf['占比(%)'] = (product_perf['amount']/product_perf['amount'].sum()*100).round(2)\n",
    "print(product_perf)\n",
    "\n",
    "# 3. 地区分析\n",
    "print(\"\\n【三、地区分析】\")\n",
    "region_perf = df.groupby('region')['amount'].sum().sort_values(ascending=False)\n",
    "print(region_perf)\n",
    "\n",
    "# 4. 客户分析\n",
    "print(\"\\n【四、TOP客户】\")\n",
    "top_customers = df.groupby('customer').agg({\n",
    "    'amount': 'sum',\n",
    "    'order_id': 'count'\n",
    "}).sort_values('amount', ascending=False).head()\n",
    "print(top_customers)\n",
    "\n",
    "# 5. 时间趋势\n",
    "print(\"\\n【五、月度趋势】\")\n",
    "monthly_trend = df.groupby('month')['amount'].sum()\n",
    "monthly_trend.index = [f\"{i}月\" for i in monthly_trend.index]\n",
    "print(monthly_trend)\n",
    "\n",
    "print(\"\\n\" + \"=\"*70)\n",
    "print(\"报告完成\")\n",
    "print(\"=\"*70)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 八、本讲总结\n",
    "\n",
    "### 核心知识点\n",
    "\n",
    "1. **groupby()机制**\n",
    "   - Split-Apply-Combine原理\n",
    "   - 单列和多列分组\n",
    "   - 分组对象的操作\n",
    "\n",
    "2. **聚合函数**\n",
    "   - 单一聚合: `sum()`, `mean()`, `count()`等\n",
    "   - `agg()`多函数聚合\n",
    "   - 自定义聚合函数\n",
    "   - 命名聚合\n",
    "\n",
    "3. **数据透视表**\n",
    "   - `pivot_table()`基础用法\n",
    "   - 多级索引透视表\n",
    "   - margins参数显示总计\n",
    "   - 透视表可视化\n",
    "\n",
    "4. **派生指标**\n",
    "   - 同比/环比增长率\n",
    "   - 占比和累计占比\n",
    "   - 移动平均\n",
    "   - 排名\n",
    "\n",
    "5. **实战应用**\n",
    "   - RFM客户分层\n",
    "   - 销售漏斗分析\n",
    "   - 帕累托分析\n",
    "\n",
    "### 关键函数速查\n",
    "\n",
    "```python\n",
    "# 分组聚合\n",
    "df.groupby('col').sum()\n",
    "df.groupby(['col1', 'col2']).agg({'col3': 'sum'})\n",
    "\n",
    "# 数据透视\n",
    "pd.pivot_table(df, values='val', index='row', columns='col')\n",
    "\n",
    "# 派生指标\n",
    "df['pct_change'] = df['col'].pct_change()  # 环比\n",
    "df['cumsum'] = df['col'].cumsum()          # 累计\n",
    "df['rolling'] = df['col'].rolling(7).mean() # 移动平均\n",
    "```\n",
    "\n",
    "### 下节预告\n",
    "**第4讲: 探索性数据分析(EDA)**\n",
    "- 单变量/双变量/多变量分析\n",
    "- 相关性分析\n",
    "- 异常模式识别\n",
    "- 完整EDA报告\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 课后作业\n",
    "\n",
    "### 作业1: 多维度分析\n",
    "使用本讲的数据集`df`,完成:\n",
    "1. 创建一个地区×产品×月份的三维透视表\n",
    "2. 找出每个地区销售额最高的产品\n",
    "3. 计算每个销售人员的业绩排名\n",
    "4. 分析折扣对销售额和利润的影响\n",
    "\n",
    "### 作业2: RFM实战\n",
    "1. 完善RFM模型,增加更细致的客户分层(8层)\n",
    "2. 为每层客户设计营销策略\n",
    "3. 计算每层客户的价值贡献\n",
    "4. 可视化RFM分布\n",
    "\n",
    "### 作业3: 综合仪表板\n",
    "创建一个综合分析仪表板,包含:\n",
    "1. 关键指标卡片(总销售额、订单数、客单价等)\n",
    "2. 产品销售对比(柱状图)\n",
    "3. 地区销售热力图\n",
    "4. 月度趋势图(含移动平均线)\n",
    "5. TOP10客户表格\n",
    "6. 帕累托图(产品贡献分析)\n",
    "\n",
    "### 作业4: Excel对比练习\n",
    "1. 用Excel创建同样的数据透视表\n",
    "2. 对比操作步骤和耗时\n",
    "3. 总结Pandas和Excel各自的优劣\n",
    "4. 提出混合使用的最佳方案"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
